/*
 * To change e template, choose Tools | Templates
 * and open the template in the editor.
 */
package wargame.dao;

import wargame.entity.*;
import connectionmanager.DB;
import java.util.ArrayList;

/**
 *
 * @author Clarence
 */
public class EmployeeDAO {

    public static Employee retrieveEmployee(int employee_id) {
        String sql = "SELECT `employee_id`, `player_id`, `hours_remaining`, `skill_points_dot_net`, `skill_points_c_sharp`, `skill_points_jsp`, `skill_points_php`, `skill_points_ruby`, `skill_points_mysql`, `skill_points_mssql`, `skill_points_oracle`, `turn_hired`, `salary`, `tolerance`, `morale` FROM `employee` WHERE employee_id = " + employee_id; //player_id = " + employee_id??
        DB db = new DB();
        db.connect();
        db.query(sql);
        Employee employee = null;
        if (db.next()) {
            employee = populate(db);
        }
        db.close();
        return employee;
    }

    private static Employee populate(DB db) {
        Employee e = new Employee();
        e.setEmployee_id(db.getInt("employee_id"));
        e.setPlayer_id(db.getInt("player_id"));
        e.setHours_remaining(db.getInt("hours_remaining"));
        e.setSkillPoints_dot_net(db.getInt("skill_points_dot_net"));
        e.setSkillPoints_c_sharp(db.getInt("skill_points_c_sharp"));
        e.setSkillPoints_jsp(db.getInt("skill_points_jsp"));
        e.setSkillPoints_php(db.getInt("skill_points_php"));
        e.setSkillPoints_ruby(db.getInt("skill_points_ruby"));
        e.setSkillPoints_mysql(db.getInt("skill_points_mysql"));
        e.setSkillPoints_mssql(db.getInt("skill_points_mssql"));
        e.setSkillPoints_oracle(db.getInt("skill_points_oracle"));
        e.setTurn_hired(db.getInt("turn_hired"));
        e.setSalary(db.getInt("salary"));
        e.setTolerance(db.getDouble("tolerance"));
        e.setMorale(db.getDouble("morale"));
        return e;
    }

    public static int insertEmployee(Employee e) {
        String sql = "INSERT INTO employee (`player_id`, `hours_remaining`, "
                + "`skill_points_dot_net`, `skill_points_c_sharp`, "
                + "`skill_points_jsp`, `skill_points_php`, `skill_points_ruby`, "
                + "`skill_points_mysql`, `skill_points_mssql`, `skill_points_oracle`, "
                + "`turn_hired`, `salary`, `tolerance`, `morale`) VALUES ('"
                + e.getPlayer_id() + "', '" + e.getHours_remaining() + "', '"
                + e.getSkillPoints_dot_net() + "', '" + e.getSkillPoints_c_sharp()
                + "', '" + e.getSkillPoints_jsp() + "', '" + e.getSkillPoints_php()
                + "', '" + e.getSkillPoints_ruby() + "', '" + e.getSkillPoints_mysql()
                + "', '" + e.getSkillPoints_mssql() + "', '" + e.getSkillPoints_oracle()
                + "', '" + e.getTurn_hired() + "', '" + e.getSalary() + "', '" + e.getTolerance()
                + "', '" + e.getMorale() + "');";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        int lastInsertedId = db.getLastInsertedID();
        db.close();
        return lastInsertedId;
    }

    public static void updateEmployee(Employee e) {
        String sql = "UPDATE employee SET `player_id` = '" + e.getPlayer_id()
                + "', `hours_remaining` = '" + e.getHours_remaining()
                + "', `skill_points_dot_net` = '" + e.getSkillPoints_dot_net()
                + "', `skill_points_c_sharp` = '" + e.getSkillPoints_c_sharp()
                + "', `skill_points_jsp` = '" + e.getSkillPoints_jsp()
                + "', `skill_points_php` = '" + e.getSkillPoints_php()
                + "', `skill_points_ruby` = '" + e.getSkillPoints_ruby()
                + "', `skill_points_mysql` = '" + e.getSkillPoints_mysql()
                + "', `skill_points_mssql` = '" + e.getSkillPoints_mssql()
                + "', `skill_points_oracle` = '" + e.getSkillPoints_oracle()
                + "', `turn_hired` = '" + e.getTurn_hired()
                + "', `salary` = '" + e.getSalary()
                + "', `tolerance` = '" + e.getTolerance()
                + "', `morale` = '" + e.getMorale()
                + "' WHERE employee_id = '" + e.getEmployee_id() + "';";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        db.close();
    }

    public static void deleteEmployee(Employee e) {
        String sql = "DELETE FROM employee where employee_id=" + e.getEmployee_id();
        DB db = new DB();
        db.connect();
        db.execute(sql);
        db.close();
    }

    public static ArrayList retrievePlayerEmployees(Player p) {
        ArrayList<Employee> playerEmployees = new ArrayList<Employee>();
        String sql = "SELECT * FROM `employee` WHERE player_id = " + p.getPlayer_id();
        DB db = new DB();
        db.connect();
        db.query(sql);
        while (db.next()) {
            Employee e = populate(db);
            playerEmployees.add(e);
        }
        db.close();
        return playerEmployees;
    }

    public static int playerTotalEmployee(Player p) {
        int totalEmployee = 0;
        String sql = "Select count(*) as employee_count FROM `employee` WHERE player_id = " + p.getPlayer_id();
        DB db = new DB();
        db.connect();
        db.query(sql);
        if (db.next()) {
            totalEmployee = db.getInt("employee_count");
        } else {
            totalEmployee = 0;
        }
        db.close();
        return totalEmployee;
    }

    public static void assignEmployee(Employee e, Project p, String skill, int hour, int turn) {
        String sql = "INSERT INTO assign_project (`project_id`, `employee_id`, `skill`,`hour`,`turn` ) VALUES ('"
                + p.getProject_id() + "', '" + e.getEmployee_id() + "','" + skill + "'," + hour + "," + turn + ");";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        db.close();
    }

    public static int employeeProject(Employee e) {
        int totalProj = 0;
        String sql = "Select count(*) as project_count FROM `assign_project` WHERE employee_id = " + e.getEmployee_id();
        DB db = new DB();
        db.connect();
        db.query(sql);
        if (db.next()) {
            totalProj = db.getInt("project_count");
        } else {
            totalProj = 0;

        }
        db.close();

        return totalProj;
    }

    public static void storeTempEmployee(Employee[] e) {
        for (int i = 0; i < e.length; i++) {
            String sql = "INSERT INTO temp_employee (`dotnet`, `csharp`, `ruby`,`php`,`mysql`,`mssql`,`oracle`,`jsp`,`employed`,`salary`) VALUES ('"
                    + e[i].getSkillPoints_dot_net() + "','" + e[i].getSkillPoints_c_sharp() + "','"
                    + e[i].getSkillPoints_ruby() + "','" + e[i].getSkillPoints_php() + "','"
                    + e[i].getSkillPoints_mysql() + "','" + e[i].getSkillPoints_mssql() + "','"
                    + e[i].getSkillPoints_oracle() + "','" + e[i].getSkillPoints_jsp()
                    + "','0','" + e[i].getSalary() + "');";
            //String sql = "INSERT INTO temp_employee (`employee_id`,`dotnet`, `csharp`, `ruby`,`php`,`mysql`,`mssql`,`oracle`,`jsp`,`employed`) VALUES ('0','0','0','0','0','0','0','0','0','0');";

            DB db = new DB();
            db.connect();
            db.execute(sql);
            db.close();
        }

    }

    public static ArrayList<Employee> retrieveTempEmployee() {
        ArrayList<Employee> employees = new ArrayList<Employee>();

        String sql = "select * from temp_employee WHERE employee_id >  ((SELECT MAX(employee_id) from temp_employee) - 12);";
        DB db = new DB();
        db.connect();
        db.query(sql);
        while (db.next()) {
            Employee e = new Employee();
            e.setEmployee_id(db.getInt("employee_id"));
            e.setSkillPoints_dot_net(db.getInt("dotnet"));
            e.setSkillPoints_c_sharp(db.getInt("csharp"));
            e.setSkillPoints_ruby(db.getInt("ruby"));
            e.setSkillPoints_php(db.getInt("php"));
            e.setSkillPoints_mysql(db.getInt("mysql"));
            e.setSkillPoints_mssql(db.getInt("mssql"));
            e.setSkillPoints_oracle(db.getInt("oracle"));
            e.setSkillPoints_jsp(db.getInt("jsp"));
            e.setSalary(db.getInt("salary"));
            employees.add(e);
        }
        db.close();

        return employees;
    }

    public static ArrayList<Integer> retrieveEmployeeHour(int employeeID,int turn){
        ArrayList<Integer> hours = new ArrayList<Integer>();
        int previousTurn=turn-1;
        String sql = "Select hour FROM assign_project WHERE employee_id = " + employeeID+" AND turn ="+previousTurn;
        DB db = new DB();
        db.connect();
        db.query(sql);
        while (db.next()) {
            hours.add(db.getInt("hour"));
        }
        db.close();

        return hours;
    }

    public static Employee retrieveATempEmployee(int employee_id) {
        String sql = "select * from temp_employee WHERE employee_id='" + employee_id + "';";
        DB db = new DB();
        db.connect();
        db.query(sql);
        Employee e = null;
        while (db.next()) {
            e = new Employee();
            e.setEmployee_id(db.getInt("employee_id"));
            e.setSkillPoints_dot_net(db.getInt("dotnet"));
            e.setSkillPoints_c_sharp(db.getInt("csharp"));
            e.setSkillPoints_ruby(db.getInt("ruby"));
            e.setSkillPoints_php(db.getInt("php"));
            e.setSkillPoints_mysql(db.getInt("mysql"));
            e.setSkillPoints_mssql(db.getInt("mssql"));
            e.setSkillPoints_oracle(db.getInt("oracle"));
            e.setSkillPoints_jsp(db.getInt("jsp"));
            e.setSalary(db.getInt("salary"));
        }
        db.close();
        return e;
    }

    public static void removeTempEmployee(int employee_id) {
        String sql = "DELETE FROM temp_employee where employee_id=" + employee_id;
        DB db = new DB();
        db.connect();
        db.execute(sql);
        db.close();
    }

    public static void clear() {
        String sql = "TRUNCATE TABLE employee";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        db.close();
    }

    public static void clear_temp() {
        String sql = "TRUNCATE TABLE temp_employee";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        db.close();
    }

    public static void restore_employee_hours() {
        String sql = "UPDATE employee SET hours_remaining=50";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        db.close();
    }
}
